13 - Organizational Intelligence Skill Builders

:::

A national cinema chain has commissioned you to design a multidimensional database for its marketing department. What identifier and variable dimensions would you select?

Apply the basic prompts outlined in the book.

Identifiers When Date and time
Weather
Where Region
City
What Movie title
Rating
Genre
How reservation channel (phone, Web,booth)
Variables Outcomes Revenue
Adult tickets sold
Senior tickets sold
Child tickets sold
Adult price
Senior price
Child price
Concession sales

Write SQL to answer each of the following four queries using the EXPED table, which is a sample of 1,000 sales transactions from The Expeditioner.

Find the total revenue.

SELECT FORMAT(SUM(revenue),0) FROM exped;

Report revenue by location.

SELECT location, FORMAT(SUM(revenue),0) FROM exped GROUP BY location;

Report revenue by channel.

SELECT channel, FORMAT(SUM(revenue),0) FROM exped GROUP BY channel;

Report revenue by location and channel.

SELECT location, channel, FORMAT(SUM(revenue),0) FROM exped GROUP BY location, channel;

Using MySQL’s ROLLUP capability, report the sales by location and channel for each item.

SELECT location, channel, item, FORMAT(SUM(revenue),0) FROM exped
GROUP BY location, channel, item; 

SELECT location, channel, item, FORMAT(SUM(revenue),0) FROM exped
GROUP BY location, channel, item WITH ROLLUP;